package com.wanmait.spacesys.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.wanmait.spacesys.util.DbHelper;
import com.wanmait.spacesys.vo.Article;
import com.wanmait.spacesys.vo.Managerinfo;
import com.wanmait.spacesys.vo.PhotoNew;

public class ConsultingArticleDAO {
	//单一实例
	private ConsultingArticleDAO() {}
	private static ConsultingArticleDAO consultingArticleDAO=new ConsultingArticleDAO();
	public static ConsultingArticleDAO getConsultingArticleDAO() {
		return consultingArticleDAO;
	}
	//按照资讯区官方文章发布的时间查询照片
	public List<Article> findAllArticle(){
		List<Article> articles=new ArrayList<Article>();
		String sql="SELECT pic,id,pub_time,title FROM article  order by pub_time DESC LIMIT 0,6";
		List<Map<String,Object>> datas=DbHelper.select(sql);
		for(Map<String,Object> row:datas) {
			Article article=new Article();
			article.setPic((String)row.get("pic"));
			article.setId((Integer)row.get("id"));
			article.setPubTime((Timestamp)row.get("pub_time"));
			article.setTitle((String)row.get("title"));
			articles.add(article);
		}
		
		return articles;
	}
	//资讯区官方文章的id查询资讯区官方文章的详细信息
	public Article findById(Integer articleId) {
		Article article=null;
		Connection connection=DbHelper.getConnection();
		PreparedStatement preparedStatement=null;
		ResultSet resultSet=null;
		String sql="SELECT title,content,id,pic,pub_time FROM article WHERE id=?";
		try {
			preparedStatement=connection.prepareStatement(sql);
			preparedStatement.setInt(1, articleId);
			resultSet=preparedStatement.executeQuery();
			if(resultSet.next()) {
				article=new Article();
				article.setTitle(resultSet.getString("title"));
				article.setContent(resultSet.getString("content"));
				article.setId(resultSet.getInt("id"));
				article.setPic(resultSet.getString("pic"));
				article.setPubTime(resultSet.getTimestamp("pub_time"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return article;
	}
	
	//资讯区官方文章的id查询资讯区官方文章的发布人文章详细信息
	public Article findManagerById(Integer articleId) {
		Article article=null;
		Connection connection=DbHelper.getConnection();
		PreparedStatement preparedStatement=null;
		ResultSet resultSet=null;
		String sql="SELECT article.title,article.content,article.id,article.pic,article.pub_time,article.manager_id,managerinfo.nickname FROM article \r\n" + 
				"LEFT JOIN managerinfo ON article.manager_id=managerinfo.id\r\n" + 
				"WHERE article.id=?";
		try {
			preparedStatement=connection.prepareStatement(sql);
			preparedStatement.setInt(1, articleId);
			resultSet=preparedStatement.executeQuery();
			if(resultSet.next()) {
				article=new Article();
				article.setTitle(resultSet.getString("title"));
				article.setContent(resultSet.getString("content"));
				article.setId(resultSet.getInt("id"));
				article.setPic(resultSet.getString("pic"));
				article.setPubTime(resultSet.getTimestamp("pub_time"));
				Managerinfo managerinfo=new Managerinfo();
				managerinfo.setId(resultSet.getInt("manager_id"));
				managerinfo.setNickname(resultSet.getString("nickname"));
				article.setManagerinfo(managerinfo);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return article;
	}
	
		//按照最新时间显示首页精彩图片
		public List<PhotoNew> findAllPhoto(){
			List<PhotoNew> photoNews=new ArrayList<PhotoNew>();
			String sql="SELECT id,pic,upload_time FROM photo  ORDER BY upload_time DESC LIMIT 0,20";
			List<Map<String,Object>> datas=DbHelper.select(sql);
			for(Map<String,Object> row:datas) {
				PhotoNew photoNew=new PhotoNew();
				photoNew.setId((Integer)row.get("id"));
				photoNew.setPic((String)row.get("pic"));
				photoNew.setUploadTime((Timestamp)row.get("upload_time"));
				photoNews.add(photoNew);
				}
			return photoNews;
		}
		public static void main(String[] args) {
//			List<Article> articles=ConsultingArticleDAO.getConsultingArticleDAO().findAllArticle();
//			articles.forEach(article->System.out.println(article.getId()+article.getPic()+article.getPubTime()+article.getTitle()));
//			List<PhotoNew> photoNews=ConsultingArticleDAO.getConsultingArticleDAO().findAllPhoto();
//			photoNews.forEach(photoNew->System.out.println(photoNew.getId()+photoNew.getPic()+photoNew.getUploadTime()));
			Article article=ConsultingArticleDAO.getConsultingArticleDAO().findManagerById(1);
			System.out.println(article.getId()+article.getTitle()+article.getPic()+article.getContent()+article.getPubTime()+article.getManagerinfo().getNickname());
		}
}
